CREATE PROCEDURE ResidualCorrectionAccounts @OutPutId INT OUTPUT AS
BEGIN TRY
BEGIN TRAN

IF EXISTS(SELECT Name FROM sys.sysobjects WHERE Name ='TU_Account')
  ALTER TABLE dbo.Account DISABLE TRIGGER TU_Account;

------------------(      )--------------------
UPDATE dbo.Account SET Bedeh = 0 , Bestan = 0, Has_Child = 0
UPDATE dbo.Account SET Has_Child = 1 FROM Account WITH (NOLOCK)
INNER JOIN dbo.Account Detail WITH (NOLOCK) ON Detail.Parent_Code = dbo.Account.AutoId

Update Sanad Set Type = 0 Where Bedeh >0 And ISNULL(Type,'0') = 1
Update Sanad Set Type = 1 Where Bestan>0 And ISNULL(Type,'0') = 0
--------------------------------------------------------------------------------------

DECLARE @ResultTable TABLE
(
    AccountCode VARCHAR(20) PRIMARY KEY ,
    Debtor MONEY ,
    Creditor MONEY 
)


IF NOT EXISTS(SELECT Name FROM [tempdb].dbo.sysobjects WHERE Name = '#CacheValue')  
BEGIN 
 CREATE TABLE [dbo].[#CacheValue](                                                   
   [Id] INT NOT NULL ,                                 
   [ParentId] INT NULL ,                                 
   [AccountCode] VARCHAR(20) ,                                                      
   [Debtor] [MONEY] ,                                                      
   [Creditor] [MONEY] ,                                                      
  CONSTRAINT [PK_#CacheValue] PRIMARY KEY CLUSTERED                                   
  ([Id] ASC)                                                                          
  ) ON [PRIMARY]                                                                        
END 
ELSE
  TRUNCATE TABLE #CacheValue

INSERT INTO #CacheValue
SELECT Account.AutoId,
       Account.Parent_Code,
       Account.Acc_Code,
       ISNULL(T.Debtor, 0) Debtor,
       ISNULL(T.Creditor, 0) Creditor
FROM dbo.Account WITH (NOLOCK)
    LEFT OUTER JOIN
    (
        SELECT Acc_Code,
               SUM(Bedeh) Debtor,
               SUM(Bestan) Creditor
        FROM dbo.Sanad WITH (NOLOCK)
        GROUP BY Acc_Code
    ) AS T
    ON T.Acc_Code = dbo.Account.Acc_Code;

WITH C
AS (SELECT Id,
           Debtor,
           Creditor,
           Id AS RootID
    FROM #CacheValue
    UNION ALL
    SELECT Sanad.Id,
           Sanad.Debtor,
           Sanad.Creditor,
           C.RootID
    FROM #CacheValue Sanad
    INNER JOIN C ON Sanad.ParentId = C.Id
   )
INSERT INTO @ResultTable(AccountCode, Debtor, Creditor)
SELECT Sanad.AccountCode,
       S.SumDebtor,
       S.SumCreditor
FROM #CacheValue Sanad
    INNER JOIN
    (
        SELECT RootID,
               SUM(C.Debtor) AS SumDebtor,
               SUM(C.Creditor) AS SumCreditor
        FROM C
        GROUP BY RootID
    ) AS S
        ON Sanad.Id= S.RootID

UPDATE dbo.Account SET Bedeh = [@ResultTable].Debtor, Bestan = [@ResultTable].Creditor FROM dbo.Account WITH (NOLOCK)
INNER JOIN @ResultTable ON AccountCode=Acc_Code;

IF EXISTS(SELECT Name FROM sys.sysobjects WHERE Name ='TU_Account')
  ALTER TABLE dbo.Account DISABLE TRIGGER TU_Account;

COMMIT TRAN
SET @OutPutId = 1
SELECT @OutPutId
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

    DECLARE @Message nvarchar(2048) = ERROR_MESSAGE();
    DECLARE @Severity integer = ERROR_SEVERITY();
    DECLARE @State integer = ERROR_STATE();

    RAISERROR(@Message, @Severity, @State);
	SET @OutPutId = -1
    Select @OutPutId
END CATCH